DECLARE @DATE_UPDATE NVARCHAR(100)= (select [DATE_UPDATE] from [DATE_EM_REPORT])
EXEC('
IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''DEPOSIT_ALL_DAY'')
        DROP TABLE DEPOSIT_ALL_DAY

SELECT BUSINESS_DATE,PRODUCT_NAME,BRANCH_CODE,CIF,CUSTOMER_NAME,CATEGORY,PRODUCT_CODE,DAO AS DAO_SP,BAL_QD INTO DEPOSIT_ALL_DAY
FROM SERVER74.BICDATA.DBO.BALANCE_LIVE A
WHERE BUSINESS_DATE='''+@DATE_UPDATE+'''
AND APP=''DEPOSIT''
AND ((A.SEGMENT<>''KHCN'' AND A.BRANCH_CODE NOT IN (SELECT BRANCH_CODE FROM PhuongAnh.DBO.BRANCHOFF))
                        OR (A.SEGMENT IS NULL AND LEFT(A.SECTOR,1)>1))')
                        
 exec('
IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''DEPOSIT_ALL_DAY_PRODUCT'')
        DROP TABLE DEPOSIT_ALL_DAY_PRODUCT

 SELECT A.*,B.DAO AS DAO_CIF 
 , CASE WHEN PRODUCT_CODE  IN (''TD6014'') THEN ''SAVING ONLINE''
 WHEN PRODUCT_CODE  IN (''TD1057'',''TD1058'',''TD1059'',''TD1060'') THEN ''CHUNG CHI TIEN GUI GHI DANH''
 WHEN PRODUCT_CODE  IN (''TD1053'',''TD1054'') THEN ''BAO HIEM HOAN PHI''
 WHEN PRODUCT_CODE  IN (''TD1005'',''TD1007'',''TD1003'',''TD2005'',''TD2023'',''TD2025'',''TD2026'',''TD2010'',''TD2021'',''TD2011'',''TD2012'') THEN ''CHUONG TRINH SMECV''
  WHEN CATEGORY  IN (''1005'',''1007'',''1008'') THEN ''VPB BUSINESS '' END CHUONG_TRINH_SP INTO DEPOSIT_ALL_DAY_PRODUCT
 FROM DEPOSIT_ALL_DAY A
 LEFT JOIN TBL_CUSTOMER B
 ON A.CIF=B.CIF
 WHERE a.BUSINESS_DATE='''+@DATE_UPDATE+'''
 ORDER BY  CATEGORY') 
     
 EXEC('
 delete from TBL_CM_DEPOSIT

 INSERT TBL_CM_DEPOSIT
 SELECT BUSINESS_DATE
 ,CIF
 ,CUSTOMER_NAME
 ,DAO_CIF
 ,DAO_SP
 ,PRODUCT_NAME
 ,CHUONG_TRINH_SP
 ,SUM(BAL_QD) AS BAL_QD --INTO TBL_CM_DEPOSIT
  FROM DEPOSIT_ALL_DAY_PRODUCT 
 WHERE BUSINESS_DATE='''+@DATE_UPDATE+'''
  GROUP BY BUSINESS_DATE,CIF,PRODUCT_NAME,CHUONG_TRINH_SP,CUSTOMER_NAME,DAO_CIF,DAO_SP')
